package com.sea.finance.service.impl;

import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LargeDataExporter {

    // 数据库连接信息
    private static final String JDBC_URL = "jdbc:mysql://rm-bp1f55u0b1x66n0ws8o.mysql.rds.aliyuncs.com:3306/shinebed_pro?useUnicode=true&characterEncoding=utf8&useServerPrepStmts=false&rewriteBatchedStatements=true";
    private static final String USER = "ykjdata";
    private static final String PASSWORD = "chf_19960801";

    // 每次读取的行数
    private static final int BATCH_SIZE = 10000;

    /**
     * 导出数据到CSV文件
     */
    public void exportToCsv(String sql, String outputFilePath) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        BufferedWriter writer = null;

        try {
            // 获取数据库连接
            conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
            pstmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            // 关闭自动提交
            conn.setAutoCommit(false);
            // MySQL特有的设置，开启流式查询
            pstmt.setFetchSize(Integer.MIN_VALUE);

            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 创建文件写入流
            writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFilePath), "UTF-8"));

            // 写入表头
            List<String> header = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                header.add(metaData.getColumnName(i));
            }
            writer.write(String.join(",", header));
            writer.newLine();

            // 批量写入数据
            int count = 0;
            while (rs.next()) {
                List<String> row = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    Object value = rs.getObject(i);
                    row.add(value != null ? value.toString() : "");
                }
                writer.write(String.join(",", row));
                writer.newLine();

                // 每1000行刷新一次，减少内存占用
                if (++count % BATCH_SIZE == 0) {
                    writer.flush();
                    System.out.println("已导出 " + count + " 条数据");
                }
            }

            writer.flush();
            System.out.println("导出完成，共 " + count + " 条数据");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
                if (writer != null) writer.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        LargeDataExporter exporter = new LargeDataExporter();
        // 导出SQL，注意只查询需要的字段，避免不必要的数据传输
        String sql = "SELECT id FROM a_vt";
        // 导出文件路径
        String outputPath = "D:/large_data_export.csv";

        long startTime = System.currentTimeMillis();
        exporter.exportToCsv(sql, outputPath);
        long endTime = System.currentTimeMillis();

        System.out.println("导出耗时: " + (endTime - startTime) / 1000 + " 秒");
    }
}
